﻿CREATE PROCEDURE [dbo].[yaf_pageload](
                @SessionID  NVARCHAR(24),
                @BoardID    INT,
                @User       NVARCHAR(50),
                @IP         NVARCHAR(15),
                @Location   NVARCHAR(50),
                @Browser    NVARCHAR(50),
                @Platform   NVARCHAR(50),
                @CategoryID INT  = NULL,
                @ForumID    INT  = NULL,
                @TopicID    INT  = NULL,
                @MessageID  INT  = NULL)
AS
    BEGIN
        DECLARE  @UserID INT
        DECLARE  @UserBoardID INT
        DECLARE  @IsGuest TINYINT
        DECLARE  @rowcount INT
        DECLARE  @PreviousVisit DATETIME
        SET implicit_transactions  off
        IF @User IS NULL 
            OR @User = ''
        BEGIN
            SELECT @UserID = a.UserID
            FROM   yaf_User a,
                   yaf_UserGroup b,
                   yaf_Group c
            WHERE  a.UserID = b.UserID
            AND a.BoardID = @BoardID
            AND b.GroupID = c.GroupID
            AND (c.Flags & 2) <> 0
            SET @rowcount = @@ROWCOUNT
            IF @rowcount <> 1
            BEGIN
                RAISERROR ('Found %d possible guest users. Only 1 guest user should be a member of the group marked as the guest group.',16,1,@rowcount)
            END
            SET @IsGuest = 1
            SET @UserBoardID = @BoardID
        END
        ELSE
        BEGIN
            SELECT @UserID = UserID,
                   @UserBoardID = BoardID
            FROM   yaf_User
            WHERE  BoardID = @BoardID
            AND Name = @User
            SET @IsGuest = 0
        END
        -- Check valid ForumID
        IF @ForumID IS NOT NULL 
           AND NOT EXISTS (SELECT 1
                           FROM   yaf_Forum
                           WHERE  ForumID = @ForumID)
        BEGIN
            SET @ForumID = NULL
        END
        -- Check valid CategoryID
        IF @CategoryID IS NOT NULL 
           AND NOT EXISTS (SELECT 1
                           FROM   yaf_Category
                           WHERE  CategoryID = @CategoryID)
        BEGIN
            SET @CategoryID = NULL
        END
        -- Check valid MessageID
        IF @MessageID IS NOT NULL 
           AND NOT EXISTS (SELECT 1
                           FROM   yaf_Message
                           WHERE  MessageID = @MessageID)
        BEGIN
            SET @MessageID = NULL
        END
        -- Check valid TopicID
        IF @TopicID IS NOT NULL 
           AND NOT EXISTS (SELECT 1
                           FROM   yaf_Topic
                           WHERE  TopicID = @TopicID)
        BEGIN
            SET @TopicID = NULL
        END
        -- get previous visit
        IF @IsGuest = 0
        BEGIN
            SELECT @PreviousVisit = LastVisit
            FROM   dbo.yaf_User
            WHERE  UserID = @UserID
        END
        -- update last visit
        UPDATE yaf_User
        SET    LastVisit = Getdate(),
               IP = @IP
        WHERE  UserID = @UserID
        -- find missing ForumID/TopicID
        IF @MessageID IS NOT NULL
        BEGIN
            SELECT @CategoryID = c.CategoryID,
                   @ForumID = b.ForumID,
                   @TopicID = b.TopicID
            FROM   yaf_Message a,
                   yaf_Topic b,
                   yaf_Forum c,
                   yaf_Category d
            WHERE  a.MessageID = @MessageID
            AND b.TopicID = a.TopicID
            AND c.ForumID = b.ForumID
            AND d.CategoryID = c.CategoryID
            AND d.BoardID = @BoardID
        END
        ELSE
        IF @TopicID IS NOT NULL
        BEGIN
            SELECT @CategoryID = b.CategoryID,
                   @ForumID = a.ForumID
            FROM   yaf_Topic a,
                   yaf_Forum b,
                   yaf_Category c
            WHERE  a.TopicID = @TopicID
            AND b.ForumID = a.ForumID
            AND c.CategoryID = b.CategoryID
            AND c.BoardID = @BoardID
        END
        ELSE
        IF @ForumID IS NOT NULL
        BEGIN
            SELECT @CategoryID = a.CategoryID
            FROM   yaf_Forum a,
                   yaf_Category b
            WHERE  a.ForumID = @ForumID
            AND b.CategoryID = a.CategoryID
            AND b.BoardID = @BoardID
        END
        -- update active
        IF @UserID IS NOT NULL 
           AND @UserBoardID = @BoardID
        BEGIN
            IF EXISTS (SELECT 1
                       FROM   yaf_Active
                       WHERE  SessionID = @SessionID
                       AND BoardID = @BoardID)
            BEGIN
                UPDATE yaf_Active
                SET    UserID = @UserID,
                       IP = @IP,
                       LastActive = Getdate(),
                       Location = @Location,
                       ForumID = @ForumID,
                       TopicID = @TopicID,
                       Browser = @Browser,
                       Platform = @Platform
                WHERE  SessionID = @SessionID
            END
            ELSE
            BEGIN
                INSERT INTO yaf_Active
                           (SessionID,
                            BoardID,
                            UserID,
                            IP,
                            Login,
                            LastActive,
                            Location,
                            ForumID,
                            TopicID,
                            Browser,
                            Platform)
                VALUES     (@SessionID,
                            @BoardID,
                            @UserID,
                            @IP,
                            Getdate(),
                            Getdate(),
                            @Location,
                            @ForumID,
                            @TopicID,
                            @Browser,
                            @Platform)
            END
            -- remove duplicate users
            IF @IsGuest = 0
            DELETE FROM yaf_Active
            WHERE       UserID = @UserID
            AND BoardID = @BoardID
            AND SessionID <> @SessionID
        END
        -- return information
        SELECT a.UserID,
               UserFlags = a.Flags,
               UserName = a.Name,
               Suspended = a.Suspended,
               ThemeFile = a.ThemeFile,
               LanguageFile = a.LanguageFile,
               TimeZoneUser = a.TimeZone,
               PreviousVisit = @PreviousVisit,
               x.*,
               CategoryID = @CategoryID,
               CategoryName = (SELECT Name
                               FROM   yaf_Category
                               WHERE  CategoryID = @CategoryID),
               ForumID = @ForumID,
               ForumName = (SELECT Name
                            FROM   yaf_Forum
                            WHERE  ForumID = @ForumID),
               TopicID = @TopicID,
               TopicName = (SELECT Topic
                            FROM   yaf_Topic
                            WHERE  TopicID = @TopicID),
               MailsPending = (SELECT COUNT(1)
                               FROM   yaf_Mail),
               Incoming = (SELECT COUNT(1)
                           FROM   yaf_UserPMessage
                           WHERE  UserID = a.UserID
                           AND IsRead = 0),
               ForumTheme = (SELECT ThemeURL
                             FROM   yaf_Forum
                             WHERE  ForumID = @ForumID)
        FROM   yaf_User a,
               yaf_vaccess x
        WHERE  a.UserID = @UserID
        AND x.UserID = a.UserID
        AND x.ForumID = Isnull(@ForumID,0)
    END


